/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import models.Profile;
import com.mysql.jdbc.Statement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author jaime.castells
 */
public class PerfilDB {
    
    public PerfilDB(){
        
    }
    
    public ArrayList<Profile> obtenerPerfiles(){
        ArrayList<Profile> lista = new ArrayList<Profile>();
        ArrayList<String> requiredKnowledges = new ArrayList<String>();
        ArrayList<Integer> requiredKnowledgesWeights = new ArrayList<Integer>();
        ArrayList<String> requiredQualities = new ArrayList<String>();
        ArrayList<String> studies = new ArrayList<String>();
        java.sql.ResultSet rs;
        Profile a = null;
        try {
            Conexion c = new Conexion();
            Statement st = (Statement) c.getConnection().createStatement();
            rs =  st.executeQuery("SELECT * FROM profile"); 
            while (rs.next()){
                a = new Profile(rs.getInt("idProfile"),rs.getObject("area").toString(),rs.getObject("position").toString(),rs.getInt("ageMin"),rs.getInt("ageMax"),rs.getObject("gender").toString(),rs.getObject("city").toString());
                lista.add(a);
            }
        } catch (SQLException ex) {
            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        for (int i=0;i<lista.size();i++){
        	requiredKnowledges = new ArrayList<String>();
        	requiredKnowledgesWeights = new ArrayList<Integer>();
        	requiredQualities = new ArrayList<String>();
        	studies = new ArrayList<String>();
	        try {
	            Conexion c = new Conexion();
	            Statement st = (Statement) c.getConnection().createStatement();
	            rs =  st.executeQuery("SELECT * FROM required_knowledge WHERE idProfile='" + lista.get(i).getId()+"'"); 
	            while (rs.next()){
	            	String s = new String(rs.getObject("requiredKnowledge").toString());
	            	int r = rs.getInt("weight");
	                requiredKnowledges.add(s);
	                requiredKnowledgesWeights.add(r);
	            }
	        } catch (SQLException ex) {
	            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
	        }
	        try {
	            Conexion c = new Conexion();
	            Statement st = (Statement) c.getConnection().createStatement();
	            rs =  st.executeQuery("SELECT * FROM required_studies WHERE idProfile='" + lista.get(i).getId()+"'"); 
	            while (rs.next()){
	            	String s = new String(rs.getObject("study").toString());
	                studies.add(s);
	            }
	        } catch (SQLException ex) {
	            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
	        }
	        try {
	            Conexion c = new Conexion();
	            Statement st = (Statement) c.getConnection().createStatement();
	            rs =  st.executeQuery("SELECT * FROM required_qualities WHERE idProfile='" + lista.get(i).getId()+"'"); 
	            while (rs.next()){
	            	String q1 = new String(rs.getObject("quality1").toString());
	            	String q2 = new String(rs.getObject("quality2").toString());
	            	String q3 = new String(rs.getObject("quality3").toString());
	            	requiredQualities.add(q1);
	            	requiredQualities.add(q2);
	            	requiredQualities.add(q3);
	            }
	        } catch (SQLException ex) {
	            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
	        }
	        lista.get(i).setRequiredKnowledges(requiredKnowledges);
	        lista.get(i).setRequiredKnowledgesWeights(requiredKnowledgesWeights);
	        lista.get(i).setRequiredQualities(requiredQualities);
	        lista.get(i).setStudies(studies);
        }
        
        return lista;
    }
    
    public void guardarEnBase(Profile P){
        try {
            Conexion c = new Conexion();
            Statement st = (Statement) c.getConnection().createStatement();
            int id = P.getId();
            int knowledgeid = obtenerUltimoIdRequiredKnowledges();
            st.executeUpdate("INSERT INTO profile (idProfile,area,position,ageMin,ageMax,gender,city) VALUES ('"+id+"','"+P.getArea()+"','"+P.getPosition()+"','"+P.getAgeMin()+"','"+P.getAgeMax()+"','"+P.getGender()+"','"+P.getCity()+"')");
            for (int i=0;i<P.getRequiredKnowledges().size();i++)
            	st.executeUpdate("INSERT INTO required_knowledge (idKnowledge,requiredKnowledge,weight,idProfile) VALUES ('"+(knowledgeid+i)+"','"+P.getRequiredKnowledges().get(i)+"','"+P.getRequiredKnowledgesWeights().get(i)+"','"+id+"')");
            int qualityid = obtenerUltimoIdRequiredQualities();
            st.executeUpdate("INSERT INTO required_qualities (idRequiredQuality,quality1,quality2,quality3,idProfile) VALUES ('"+qualityid+"','"+P.getRequiredQualities().get(0)+"','"+P.getRequiredQualities().get(1)+"','"+P.getRequiredQualities().get(2)+"','"+id+"')");
            int studiesid = obtenerUltimoIdRequiredStudies();
            if (P.getStudies().size()!=0)
            	for (int i=0;i<P.getStudies().size();i++)
            		st.executeUpdate("INSERT INTO required_studies (idRequiredStudy,study,idProfile) VALUES ('"+(studiesid+i)+"','"+P.getStudies().get(i)+"','"+id+"')");
        } catch (SQLException ex) {
            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            
    }

    
    public static int obtenerUltimoId(){
        java.sql.ResultSet rs;
        int numero=0;
        try {
            Conexion c = new Conexion();
            Statement st = (Statement) c.getConnection().createStatement();
            rs  = st.executeQuery("SELECT idProfile FROM profile ORDER BY idProfile DESC LIMIT 0,1"); 
            while(rs.next()){
                numero = rs.getInt("idProfile");
            }
        } catch (SQLException ex) {
            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return numero+1;
    }
    
    public static int obtenerUltimoIdRequiredKnowledges(){
        java.sql.ResultSet rs;
        int numero=0;
        try {
            Conexion c = new Conexion();
            Statement st = (Statement) c.getConnection().createStatement();
            rs  = st.executeQuery("SELECT idKnowledge FROM required_knowledge ORDER BY idKnowledge DESC LIMIT 0,1"); 
            while(rs.next()){
                numero = rs.getInt("idKnowledge");
            }
        } catch (SQLException ex) {
            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return numero+1;
    }
    
    public static int obtenerUltimoIdRequiredQualities(){
        java.sql.ResultSet rs;
        int numero=0;
        try {
            Conexion c = new Conexion();
            Statement st = (Statement) c.getConnection().createStatement();
            rs  = st.executeQuery("SELECT idRequiredQuality FROM required_qualities ORDER BY idRequiredQuality DESC LIMIT 0,1"); 
            while(rs.next()){
                numero = rs.getInt("idRequiredQuality");
            }
        } catch (SQLException ex) {
            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return numero+1;
    }

    public static int obtenerUltimoIdRequiredStudies(){
        java.sql.ResultSet rs;
        int numero=0;
        try {
            Conexion c = new Conexion();
            Statement st = (Statement) c.getConnection().createStatement();
            rs  = st.executeQuery("SELECT idRequiredStudy FROM required_studies ORDER BY idRequiredStudy DESC LIMIT 0,1"); 
            while(rs.next()){
                numero = rs.getInt("idRequiredStudy");
            }
        } catch (SQLException ex) {
            Logger.getLogger(PerfilDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return numero+1;
    }
}
